package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;


import crm.dbcoon.DBConnect;
import crm.model.Customer;
import crm.util.Pagination;

public class CustomerControl {

	public static Pagination pageInstance = null;
	
	public static int addCustomer(Customer cu) {
	
		DBConnect dbc = new DBConnect();
		String sql1 ="insert into info_customer(name,address,phone,area_id,type,origin,crete_time,business,namepy,employee_id) values(?,?,?,?,?,?,?,?,?,?)" ;
		int insertId = 0;
		ResultSet rs = null;
		dbc.prepareStatement(sql1);
		System.out.println("DD"+sql1+"DD");
		try{
			setRs(dbc, cu);
			dbc.executeUpdate();
		   // rs = dbc.getPreparedStatement().getGeneratedKeys();//返回插入数据后新增行的id
			rs = dbc.getPreparedStatement().getGeneratedKeys();
			if (rs.next()) {
				insertId = rs.getInt(1);
				
			}
			return insertId;
			}catch (Exception e) {
			e.printStackTrace();
			System.out.println("addCustomer方法出错");
			return insertId;
		}finally{
			dbc.close();
		}
		
	}
	
	public static boolean deleteCustomerById(int id) {
		DBConnect dbConnect = new DBConnect();
		String sql = "delete from info_customer where id="+id;
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("deleteCustomer出错");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	
	public static boolean updateCustomer(Customer cu) {
		DBConnect dbConnect = new DBConnect();
		String sql = "update info_customer set name=?,address=?,phone=?,area_id=?,type=?,origin=?,crete_time=?,business=?,namepy=?,employee_id=? where id = '"+cu.getId()+"'";
		dbConnect.prepareStatement(sql);
		try {
			setRs(dbConnect, cu);
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("updateCustomer出错");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	public static Customer getCustomerById(int id){
		DBConnect dbConnect = new DBConnect();
		Customer cu = new Customer();
		String sql = "select * from info_customer where id = '"+id+"'";
		dbConnect.prepareStatement(sql);
		ResultSet rs = null;
		try {
			 rs =(ResultSet) dbConnect.executeQuery();
			 if(rs.next()){
				 getRs(rs, cu);
			 }
			 return cu;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getCustomerById出错");
			return null;
		}finally{
			dbConnect.close();
		}
	}
	
	public static int getID(){
		DBConnect dbc = new DBConnect();
		String sql="select @@IDENTITY";
		dbc.prepareStatement(sql);
		try {
			
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			return rs.getInt("IDENTITY");
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			dbc.close();
		}
		return 0;
	}
	

	
	

	public static void setRs(DBConnect dbc,Customer cu) {
		//调用此方法需注意字段顺序
		int index=1;
		try{
		
		dbc.setString(index++, cu.getName());
		dbc.setString(index++, cu.getAddress());
		dbc.setString(index++, cu.getPhone());
		dbc.setInt(index++, cu.getArea_id());
		dbc.setString(index++, cu.getType());
		dbc.setString(index++, cu.getOrigin());
		dbc.setString(index++, cu.getCrete_time());
		dbc.setString(index++, cu.getBusiness());
		dbc.setString(index++, cu.getNamepy());
		dbc.setInt(index++, cu.getEmployee_id());
	}catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		System.out.println("setRs方法出错");
	}
	}
	
	
	public static void getRs(ResultSet rs,Customer cu) {
		try {
			cu.setId(rs.getInt("id"));
			cu.setName(rs.getString("name"));
			cu.setAddress(rs.getString("address"));
			cu.setPhone(rs.getString("phone"));
			cu.setArea_id(rs.getInt("area_id"));
			cu.setType(rs.getString("type"));
			cu.setOrigin(rs.getString("origin"));
			cu.setCrete_time(rs.getString("crete_time"));
			cu.setBusiness(rs.getString("business"));
			cu.setNamepy(rs.getString("namepy"));
			cu.setEmployee_id(rs.getInt("employee_id"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getRs方法出错");
		}
		
	}
	
	public static List<Customer> getCustomersByEmployeeId(int employeeId){
		List<Customer> cs = new ArrayList<Customer>();
		DBConnect dbConnect = new DBConnect();
		String sql = "select * from info_customer where employee_id ='"+employeeId+"'";
		ResultSet rSet = null;
		dbConnect.prepareStatement(sql);
		try {
			rSet = dbConnect.executeQuery();
			while(rSet.next()){
				Customer customer = new Customer();
				getRs(rSet, customer);
				cs.add(customer);
			}
			return cs;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("获取单个销售员客户失败");
		}finally{
			dbConnect.close();
		}
		return null;
	}
	
	public  static Collection getAllCustomer(String name,String area_id, String type,String crete_time,String business,String cid, int pages,int rowPerPage){
		
		if(name==null||name.equals(""))
			name="%";
		if( area_id==null|| area_id.equals(""))
			area_id="%";
		if(type==null||type.equals(""))
			type="%";
		if(crete_time==null||crete_time.equals(""))
			crete_time="%";
		if(business==null||business.equals(""))
			business="%";
		if (cid==null||cid.equals(""))
			cid="%";
		String sql="select info_customer.* from info_customer,param_area where info_customer.name like '%"+name+"%'" +
		" and info_customer.area_id = param_area.id and param_area.id like '%"+area_id+"%'"+
				" and info_customer.crete_time like '%"+crete_time+"%'"+"and info_customer.type like '%"+type+"%'"+
						" and info_customer.business like '"+business+"'"+
						"and employee_id like '"+cid+"'";
		System.out.println(sql);
		pageInstance = new CustomerControlPage(sql, rowPerPage);
		try {
			return pageInstance.getPage(pages);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}
	public Collection getCustomer(int page, int rowsPerPage) throws SQLException, Exception
	{
		String sql = "SELECT * FROM info_customer";
		pageInstance = new CustomerControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}
	
	public Collection getCustomer(int pages,int rowsPerpage,String time) throws SQLException,Exception
	{
		String sql = "select * from info_customer order by "+time+" desc";
		pageInstance = new CustomerControlPage(sql, rowsPerpage);
		return pageInstance.getPage(pages);
	}
	
	public Collection getCustomer_1(int page, int rowsPerPage,String type,String key) throws SQLException, Exception
	{
		String sql = "SELECT * FROM info_customer where "+type+" like '%"+key+"%' ";
		System.out.println(sql);
		pageInstance = new CustomerControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}
	
	/*
	 * 添加hashmap 
	 * lihang
	 */
	public static HashMap getAllCustomers(){
		DBConnect dbc =new DBConnect();
	     ResultSet rs = null;
	     HashMap customers = new HashMap();
	     String sql = "select * from info_customer";
	     dbc.prepareStatement(sql);
	     try {
			rs = dbc.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				customers.put(id, name);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return customers;
	}
}

class CustomerControlPage extends Pagination {
	
	public CustomerControlPage(String sql,int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	
	protected Collection<Customer> packResultSet(ResultSet rs) {
		Collection<Customer> customers = new ArrayList<Customer>();
		try{
			while(rs.next()){
				Customer cu =new Customer();
				CustomerControl.getRs(rs, cu);
				customers.add(cu);
				cu = null;
			}
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Collection<customer>出错");
		}
		return customers;
	}

}
